package day13.homework;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

public class StudentDemo {
//	System.out.println("-------------------------");
//	System.out.println("输入1：展示所有的学生");
//	System.out.println("输入2：添加学生");
//	System.out.println("输入3：根据id删除学生");
//	System.out.println("输入4：根据条件搜索学生信息");
//	System.out.println("输入5：根据id修改学生信息");
//	System.out.println("-------------------------");

	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		while(true) {
			System.out.println("-------------------------");
			System.out.println("输入0：退出程序");
			System.out.println("输入1：展示所有的学生");
			System.out.println("输入2：添加学生");
			System.out.println("输入3：根据id删除学生");
			System.out.println("输入4：根据条件搜索学生信息");
			System.out.println("输入5：根据id修改学生信息");
			System.out.println("-------------------------");
			int type = scanner.nextInt();
			while(type > 5 || type < 0) {
				System.out.println("请重新输入：");
				if (type >= 0 && type <= 5) {
					break;
				}
				type = scanner.nextInt();
			}
			Connection connection = null;
			PreparedStatement statement = null;
			ResultSet resultSet = null;
			try {
				connection = JDBCUtil.getConnection();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			switch (type) {
			// 展示所有学生
			case 1:
				try {
					String sql = "SELECT * FROM student";
					statement = connection.prepareStatement(sql);
					resultSet = statement.executeQuery();
					ArrayList<Student> list = new ArrayList<Student>();
					// Moves the cursor forward one row from its current position.
					// Returns:true if the new current row is valid; false if there are no more rows
					while (resultSet.next()) {
						int id = resultSet.getInt("id");
						String name = resultSet.getString("name");
						int age = resultSet.getInt("age");
						String gender = resultSet.getString("gender");
						Student student = new Student(id, name, age, gender);
						list.add(student);
					}
					for (Student student : list) {
						System.out.println(student);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				break;
			// 添加学生
			case 2:
				System.out.println("输入新添加学生的名字：");
				String addName = scanner.next();
				System.out.println("输入新添加学生的年龄：");
				Integer addAge = scanner.nextInt();
				System.out.println("输入新添加学生的性别：");
				String addGender = scanner.next();
				try {
					String sql = "INSERT INTO student(`name`,age,gender) VALUES(?,?,?)";
					statement = connection.prepareStatement(sql);
					statement.setString(1, addName);
					statement.setInt(2, addAge);
					statement.setString(3, addGender);
					int count = statement.executeUpdate();
					System.out.println(statement);
					System.out.println("count:" + count);
				} catch (SQLException e) {
					e.printStackTrace();
				} 
				break;
			// 根据id删除学生
			case 3:
				System.out.println("输入要删除的学生id：");
				Integer delId = scanner.nextInt();
				try {
					String sql = "DELETE FROM student WHERE id=?";
					statement = connection.prepareStatement(sql);
					statement.setInt(1,	delId);
					int count = statement.executeUpdate();
					System.out.println(statement);
					System.out.println("count:" + count);
				} catch (SQLException e) {
					e.printStackTrace();
				}
				break;
			// 根据条件搜索学生
			case 4:
				System.out.println("1---------根据id查询学生");
				System.out.println("2---------根据名字查询学生");
				System.out.println("3---------根据age查询学生");
				System.out.println("4---------根据性别查询学生");
				Integer type2 = scanner.nextInt();
				while(type2 > 4 || type2 < 1) {
					System.out.println("请重新输入：");
					if (type2 >= 0 && type2 <= 5) {
						break;
					}
					type2 = scanner.nextInt();
				}
				String stringType = "";
				if(type2.equals(1)) {
					stringType = "id";
				}
				if(type2 == 2) {
					stringType = "name";
				}
				if(type2 == 3) {
					stringType = "age";
				}
				if(type2 == 4) {
					stringType = "gender";
				}
				System.out.println("输入要查询类别的内容：");
				String selectString = scanner.next();
				try {
					String sql = "SELECT id,name,age,gender FROM student WHERE " + stringType + "=?";
					statement = connection.prepareStatement(sql);
					statement.setString(1, selectString);
					resultSet = statement.executeQuery();
					ArrayList<Student> list = new ArrayList<Student>();
					while (resultSet.next()) {
						int id = resultSet.getInt("id");
						String name = resultSet.getString("name");
						int age = resultSet.getInt("age");
						String gender = resultSet.getString("gender");
						Student student = new Student(id, name, age, gender);
						list.add(student);
					}
					for (Student student : list) {
						System.out.println(student);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				break;
			// 根据id修改学生信息
			case 5:
				System.out.println("输入要修改的学生id：");
				Integer changeId = scanner.nextInt();
				System.out.println("输入修改后的名字：");
				String changeName = scanner.next();
				System.out.println("输入修改后的年龄：");
				Integer changeAge = scanner.nextInt();
				System.out.println("输入修改后的性别：");
				String changeGender = scanner.next();
				try {
					String sql = "UPDATE student SET `name`=?,`age`=?,gender=? WHERE id=?";
					statement = connection.prepareStatement(sql);
					statement.setString(1, changeName);
					statement.setInt(2, changeAge);
					statement.setString(3, changeGender);
					statement.setInt(4, changeId);
					int count = statement.executeUpdate();
					System.out.println(statement);
					System.out.println("count:" + count);
				} catch (SQLException e) {
					e.printStackTrace();
				}
				break;
			// 关闭程序
			case 0:
				JDBCUtil.close(connection, statement, resultSet);
				System.out.println("已退出");
				return;
			default:
				break;
			}
		}
	}
}
